from django.shortcuts import render

# Create your views here.
from django.db.models import Avg,Max,Min,Count,Sum
from django.http import request,HttpResponse
from shop import models

def welcome(request):

    return HttpResponse("welcome to.")

def all(request):
    res = models.Prod.objects.filter()
    res_list = []
    for item in res:
        prod_dict = {
            "prod_id": item.prod_id,
            "vend_id": item.vend_id,
            "prod_name": item.prod_name,
            "prod_price": item.prod_price,
            "prod_desc": item.prod_desc
        }
        res_list.append(prod_dict)
    return render(request,"count.html",locals())

def count(request):
    '''聚合查询'''
    item_avg = models.Prod.objects.aggregate(Avg("prod_price"))
    item_avg_alias = models.Prod.objects.aggregate(avg=Avg("prod_price"))    # aggregate(别名 = 聚合函数名("属性名称"))
    print(type(item_avg),item_avg)            # <class 'dict'> {'prod_price__avg': 16.133571}

    item_count = models.Prod.objects.aggregate(count=Count("prod_name"),max=Max("prod_price"),min=Min("prod_price"))
    print(item_count)       # {'count': 14, 'max': Decimal('55.00'), 'min': Decimal('2.50')}
    return HttpResponse(repr(item_avg_alias))    # HttpResponse支持string

def annotate(request):
    '''
    分组查询
    1、分组后，用 values 取值，则返回值是 QuerySet 数据类型里面为一个个字典；
    <QuerySet [{'vend_id': 1001, 'min': Decimal('5.99')}, {'vend_id': 1002, 'min': Decimal('3.42')},
    {'vend_id': 1003, 'min': Decimal('2.50')}, {'vend_id': 1005, 'min': Decimal('35.00')}]>

    2、用 values_list 取值，则返回值是 QuerySet 数据类型里面为一个个元组；
    <QuerySet [(1001, Decimal('5.99')), (1002, Decimal('3.42')), (1003, Decimal('2.50')), (1005, Decimal('35.00'))]>

    3、values()在annotate()之前：values()指定以什么字段进行分组，annotate()进行执行；
    4、values()在annotate之后，annotate按当前的主键分组，values定义查询的字段：
    <QuerySet [{'vend_id': 1001}, {'vend_id': 1001}, {'vend_id': 1001}...{'vend_id': 1005}]>
    '''
    # 查询每个供应商商品的最低售价，SELECT vend_id,MIN(prod_price) min FROM `products` GROUP BY vend_id;
    val_anno = models.Prod.objects.values_list("vend_id").annotate(min=Min("prod_price"))
    # 一对多模型中使用，一对一等同于select
    anno_value = models.Prod.objects.filter().annotate(count=Count("prod_name")).values("vend_id","count")
    # 按供应商个数反向排序
    item = models.Prod.objects.filter().values("vend_id").annotate(count=Count("prod_name")).order_by("-count")
    print(item,sep="\n")
    return HttpResponse(item)

from django.db.models import F,Q

def f_select(request):
    '''
    F()查询
    可以比较同一个Model中不同字段的值
    动态获取对象字段的值，可以进行运算
    '''
    # 查询价格大于等于数量的商品
    item = models.Prod.objects.filter(prod_price__gte=F("prod_count")).values("prod_name","prod_price","prod_count")
    # 将所有商品价格提高0.5元
    update_price = models.Prod.objects.update(prod_price=F("prod_price") + 0.5)

    return HttpResponse(item)

def q_select(request):
    '''
    Q()查询
    优先级从高到低：~ & |
    '''
    item = models.Prod.objects.filter(Q(prod_desc__contains="anvil") & Q(prod_price__gt=12)).values("prod_name","prod_price","prod_desc")
    res = models.Prod.objects.filter(~Q(vend_id__in=[1001,1003])).values("prod_name","vend_id")
    # Q 对象和关键字混合使用，Q 对象要在所有关键字的前面
    query = models.Prod.objects.filter(~Q(vend_id__in=[1001,1003]) & Q(prod_name__icontains="jetpack"),prod_price__gt=50).values("prod_name","vend_id","prod_id","prod_price")
    return HttpResponse(query)
